package sql

import (
	"bs/logger"
	"errors"

	//"os"
	"fmt"
	"time"

	//"github.com/joho/godotenv"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

type Config struct {
	DBUser     string
	DBPassword string
	DBHost     string
	DBPort     string
	DBName     string
}

type DB struct {
	db *gorm.DB
}

func LoadConfig() Config {
	// err := godotenv.Load()
	// if err != nil {
	// 	logger.Logger.Error("Error loading .env file: ", err)
	// 	os.Exit(1)
	// }

	return Config{
		DBUser:     "root",      //os.Getenv("DB_USER"),
		DBPassword: "my-secret-pw",    //"my-secret-pw", //os.Getenv("DB_PASSWORD"),
		DBHost:     "localhost", //os.Getenv("DB_HOST"),
		DBPort:     "3306",      //os.Getenv("DB_PORT"),
		DBName:     "newdb",     //os.Getenv("DB_NAME"),
	}
}
func NewDB() (*DB, error) {
	cfg := LoadConfig()
	if cfg.DBUser == "" || cfg.DBPassword == "" || cfg.DBHost == "" || cfg.DBPort == "" || cfg.DBName == "" {
		return nil, errors.New("database configuration is incomplete")
	}
	dsn := cfg.DBUser + ":" + cfg.DBPassword + "@tcp(" + cfg.DBHost + ":" + cfg.DBPort + ")/" + cfg.DBName + "?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
		DisableForeignKeyConstraintWhenMigrating: false, // 必须为 false 以启用外键
	})
	if err != nil {
		logger.Logger.Error("Error connecting to database: ", err)
		return nil, err
	}
	err = db.AutoMigrate(
		&User{},
		&Event{},
		&Registration{},
		&Result{},
	)

	// 处理 ENUM 类型的兼容性问题
	db.Exec("ALTER TABLE users MODIFY role ENUM('admin','teacher','student') NOT NULL")
	db.Exec("ALTER TABLE registrations MODIFY gender ENUM('male','female') NOT NULL")
	logger.Logger.Info("Connected to database successfully!")
	return &DB{db: db}, nil
}

// 查询赛事列表
func (db *DB) GetEvents(page int, pageSize int) ([]Event, int32, error) {
	var events []Event
	var total int64
	// 获取总数
	if err := db.db.Model(&Event{}).Count(&total).Error; err != nil {
		return nil, 0, err
	}
	offset := (page - 1) * pageSize // 计算偏移量

	if err := db.db.Offset(offset).Limit(pageSize).Find(&events).Error; err != nil {
		logger.Logger.Error("Error getting events: ", err)
		return nil, 0, err
	}
	return events, int32(total), nil
}


//创建赛事
func (db *DB) CreateEvent(event *Event) error {
	if err := db.db.Create(event).Error; err!= nil {
		logger.Logger.Error("Error creating event: ", err)
		return err
	}
	return nil
}

//更新赛事
func (db *DB) UpdateEvent(event *Event, id int) error {
    var existingEvent Event

    // 查询现有事件
    if err := db.db.First(&existingEvent, id).Error; err != nil {
        logger.Logger.Error("Error getting event: ", err)
        return err
    }

    // 更新现有事件的字段
    existingEvent.Name = event.Name
    existingEvent.StartTime = event.StartTime
    existingEvent.EndTime = event.EndTime
    existingEvent.Rules = event.Rules

    // 保存更新后的事件
    if err := db.db.Save(&existingEvent).Error; err != nil {
        logger.Logger.Error("Error updating event: ", err)
        return err
    }

    return nil
}

func (db *DB) GetEventByID(id int) (*Event, error) {
	var event Event
	if err := db.db.First(&event, id).Error; err!= nil {
		logger.Logger.Error("Error getting event: ", err)
		return nil, err
	}
	return &event, nil
}

//查看报名者
func (db *DB) GetRegistrations(page, pageSize int) ([]Registration, int32, error) {
	var registrations []Registration
	var total int64	
	// 获取总数
	if err := db.db.Model(&Registration{}).Count(&total).Error; err!= nil {
		logger.Logger.Error("Error getting registrations: ", err)
		return nil, 0, err
	}
	offset := (page - 1) * pageSize // 计算偏移量

	if err := db.db.Offset(offset).Limit(pageSize).Find(&registrations).Error; err!= nil {
		logger.Logger.Error("Error getting registrations: ", err)
		return nil, 0, err
	}
	return registrations, int32(total), nil
}

//创建报名
func (db *DB) CreateRegistration(registration *Registration) error {
	if err := db.db.Create(registration).Error; err!= nil {
		logger.Logger.Error("Error creating registration: ", err)
		return err
	}
	return nil
}

//查询成绩表
func (db *DB) GetResults() ([]Result, error) {
	var result []Result
	if err := db.db.Model(&Result{}).Find(result).Error;err !=nil{
		logger.Logger.Error("Error get results")
		return nil ,err
	}
	return result,nil
}

//查询所有成绩
//定义接收查询结果的结构体
type ResultDetail struct {
    ResultID  uint   `json:"result_id"`
    Username  string `json:"username"`
    EventName string `json:"event_name"`
    Score     string `json:"score"`
}
func (db *DB) GetResult(page, pageSize int) ([]ResultDetail, int, error) {
    var details []ResultDetail
    var total int64

    // 1. 创建基础查询
    query := db.db.Model(&Result{}).
        Select(`
            results.id AS result_id,
            users.username,
            events.name AS event_name,
            results.score
        `).
        Joins("JOIN registrations ON results.registration_id = registrations.id").
        Joins("JOIN users ON registrations.user_id = users.id").
        Joins("JOIN events ON results.event_id = events.id")

    if err := query.Count(&total).Error; err != nil {
        return nil, 0, fmt.Errorf("查询总数失败: %v", err)
    }

    err := query.Offset((page - 1) * pageSize).
        Limit(pageSize).
        Scan(&details).Error

    if err != nil {
        return nil, 0, fmt.Errorf("查询结果失败: %v", err)
    }

    return details, int(total), nil
}

//创建成绩
func (db *DB) CreateResult(name,score string,eventID uint) error {
	//通过name查询registration_id
	var registration Registration
	if err := db.db.Where("name = ?", name).First(&registration).Error; err!= nil {
		logger.Logger.Error("Error getting registration: ", err)
		return err
	}
	//通过event_id查询event
	var event Event
	if err := db.db.Where("id =?", eventID).First(&event).Error; err!= nil {
		logger.Logger.Error("Error getting event: ", err)
		return err
	}
	//创建成绩
	result := &Result{
		RegistrationID: registration.ID,
		EventID: uint(eventID),
		Event: event,
		Score: score,
		Registration: registration,
	}

	if err := db.db.Create(result).Error; err!= nil {
		logger.Logger.Error("Error creating result: ", err)
		return err	
	}
	return nil
}

//查看所有用户
func (db *DB) GetUsers(page, pageSize int) ([]User, int, error) {
	var users []User
	var total int64	
	// 获取总数
	if err := db.db.Model(&User{}).Count(&total).Error; err!= nil {
		logger.Logger.Error("Error getting users: ", err)
		return nil, 0, err
	}

	offset := (page - 1) * pageSize // 计算偏移量
	if err := db.db.Offset(offset).Limit(pageSize).Find(&users).Error; err!= nil {
		logger.Logger.Error("Error getting users: ", err)
		return nil, 0, err	
	}
	return users, int(total), nil
}

//创建用户
func (db *DB) CreateUser(user *User) error {
	if err := db.db.Create(user).Error; err!= nil {
		logger.Logger.Error("Error creating user: ", err)
		return err
	}
	return nil
}

//通过username查询用户
func (db *DB) GetUserByUsername(username string) (*User, error) {
	var user User
	if err := db.db.Where("name =?", username).First(&user).Error; err!= nil {
		logger.Logger.Error("Error getting user: ", err)
		return nil, err
	}
	return &user, nil
}

//修改用户
func (db *DB) UpdateUser(name, role string, id int) error {
    var user User

    // 查询现有用户
    if err := db.db.First(&user, id).Error; err != nil {
        logger.Logger.Error("Error getting user: ", err)
        return err
    }

    // 更新用户的字段
    user.Name = name
    user.Role = role

    // 保存更新后的用户
    if err := db.db.Save(&user).Error; err != nil {
        logger.Logger.Error("Error updating user: ", err)
        return err
    }
    
    return nil
}

//删除用户
func (db *DB) DeleteUser(id uint) error {
	if err := db.db.Delete(&User{}, id).Error; err!= nil {
		logger.Logger.Error("Error deleting user: ", err)
		return err
	}	
	return nil
}

type OrderSheetData struct {
    Name             string
    Age              int
    Gender           string
    Group            string
    RegistrationTime time.Time
    EventName        string
}

func(db *DB) GetRegistrationsByGender( gender string) ([]OrderSheetData, error) {
    var results []OrderSheetData

    err := db.db.Model(&Registration{}).
        Select(`
            registrations.name,
            registrations.age,
            registrations.gender,
            registrations.group_name as group,
            registrations.registration_time,
            events.name as event_name
        `).
        Joins("LEFT JOIN events ON registrations.event_id = events.id").
        Where("registrations.gender = ?", gender).
        Order("registrations.registration_time ASC").
        Scan(&results).Error

    return results, err
}

